package com.coamc.xlsunit;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class XLSParser {
	DBAccess db;
	Workbook book ;
	XLSLoader loader;
	String file;
	RowHolderFacotoy rowFactory = null;
	ExpressionVal  expressionVal = ExpressionVal.instance();

	
	public XLSParser(XLSLoader loader,String file,DBAccess db,RowHolderFacotoy rowFactory){
		this.loader = loader;
		this.file = file;
		this.db = db;
		this.rowFactory = rowFactory;
		
	}
	
	
	
	
	
	
	/**
	 * 设置初始化数据库,并
	 */
	public void init(VariableTable vars){
		vars.setXLSParser(this);
		loadWorkbook();
		//读取第二个sheet
		Sheet sheet = book.getSheetAt(1);
		String name = sheet.getSheetName();
		
		XLSSheetReader rowIterator = new XLSSheetReader(){

			@Override
			protected RowHolder getRowHolder(XLSParser parser, VariableTable vars, String sheetName) {
				return rowFactory.getInputHolder(parser, vars, sheetName);
			}
			
		};
		
		rowIterator.readSheet(this, book, name, vars,3);
		
		
		
	}
	
	
	protected void init(VariableTable vars,String sheetName){
		loadWorkbook();
	
		Sheet sheet = book.getSheet(sheetName);
		if(sheet==null){
			throw new RuntimeException("不存在 "+sheetName+" in "+file);
		}
		
		String name = sheet.getSheetName();
		XLSSheetReader rowIterator = new XLSSheetReader(){

			@Override
			protected RowHolder getRowHolder(XLSParser parser, VariableTable vars, String sheetName) {
				return rowFactory.getInputHolder(parser, vars, sheetName);
			}
			
		};
		
		rowIterator.readSheet(this, book, name, vars,3);
		
		
		
	}
	
	public void prepare(String name,VariableTable vars){
		loadWorkbook();
		XLSSheetReader rowIterator = new XLSSheetReader(){

			@Override
			protected RowHolder getRowHolder(XLSParser parser, VariableTable vars, String sheetName) {
				return rowFactory.getOutputHolder(parser, vars, sheetName);
			}
			
		};
		
		rowIterator.readSheet(this, book, name, vars,1);
		
	}
	
	
	public void test(String name,VariableTable vars){
		
		XLSSheetReader rowIterator = new XLSSheetReader(){

			@Override
			protected RowHolder getRowHolder(XLSParser parser, VariableTable vars, String sheetName) {
				return rowFactory.getOutputHolder(parser, vars, sheetName);
			}
			
		};
		
		rowIterator.readSheet(this, book, name, vars,2);
		
	}
	
	
	public Object eval(VariableTable vars,String expression){
		return expressionVal.calc(vars, expression);
	}
	
	protected void loadWorkbook(){
		if(this.book==null){
			book = loader.getWorkbook(file);
		}
	}
	
	protected boolean isVarDeclare(String stat){
		char c = stat.charAt(0);
		if(Character.isLowerCase(c)||Character.isDigit(c)){
			return true;
		}else{
			return false;
		}
	}
	
	protected boolean isIncludeSheet(String stat){
		return stat.startsWith("includeSheet");
	}
	
	
	
	protected boolean isComment(String stat){
		return stat.charAt(0)==':';
	}
	
	protected boolean isExpress(String stat){
		return stat.startsWith("{")&&stat.endsWith("}");
	}
	
	protected String removeStatementPreffix(String str){
		 return str.substring(2);
	 }
	 
	 protected String removeVarPreffix(String str){
		 return str.substring(1);
	 }
	 
	 protected String removePKPreffix(String str){
		 return str.substring(1);
	 }
	 
	 protected boolean isEmptyRow(Row row){
		//判断是否是空行，不知道有没有更好的办法
		if( row==null||row.getLastCellNum()<1){
			return true;
		}else{
			Cell c0 = row.getCell(0);
			Cell c1 = row.getCell(1);
			return isEmptyCell(c0)&&isEmptyCell(c1);
				
			}
			
	}

	 
	 protected boolean isEmptyCell(Cell cell){
			if(cell==null){
				return true;
			}else{
				Object obj = getCellValue(cell);
				if(obj==null){
					return true;
				}else{
					String str =obj.toString();
					if(StringUtils.isEmpty(str)){
						return true;
					}else{
						return false;
					}
					
				}
			}
	  }
	 
	 public Object getCeelValue(String col,String rowNo){
		Sheet sheet = book.getSheetAt(1);
		//从0开始
		Row row = sheet.getRow(Integer.parseInt(rowNo)-1);			
		Cell cell = row.getCell(convertRowNo(col)-1);
		return this.getCellValue(cell);
	 
	 }
	 
	 /**
	  * 将AB这种26进制转为二进制
	  * @param rowNo
	  * @return
	  */
	 private int convertRowNo(String s){
	    int n = 0;
	    for (int i = s.length() - 1, j = 1; i >= 0; i--, j *= 26){
	        char c = Character.toUpperCase(s.charAt(i)) ;
	        if (c < 'A' || c > 'Z'){
	        		throw new IllegalArgumentException("错误参数 "+s);
	        }
	        n += ((int)c - 64) * j;
	    }
	    return n;
	 }
	
	 protected   String getCellValueAsString(Cell cell){
		 Object obj = getCellValue(cell);
		 if(obj==null){
			 return null;
		 }else{
			 return obj.toString();
		 }
	 }
	 
	 protected  Object getCellValue(Cell cell) {
		if(cell==null) return null;
		 switch(cell.getCellType()){
		 case Cell.CELL_TYPE_NUMERIC:{
			 if (HSSFDateUtil.isCellDateFormatted(cell)) {
		    		Date  date = cell.getDateCellValue();
//		    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//		    		return sdf.format(date);
		    		return date;
		        }else{
		        	 Number number =  cell.getNumericCellValue();
					 return number;
		        }
		 }
		 case Cell.CELL_TYPE_STRING:{
			 return cell.getStringCellValue();
		 }
		 case Cell.CELL_TYPE_BLANK:{
			 return null;
		 }
		 case Cell.CELL_TYPE_FORMULA:{
			 //TODO 待验证
			 FormulaEvaluator evaluator = book.getCreationHelper().createFormulaEvaluator();
			 CellValue cellValue = evaluator.evaluate(cell);
			 switch (cellValue.getCellType()) {
			    case Cell.CELL_TYPE_BOOLEAN:
			        return cellValue.getBooleanValue();
			    case Cell.CELL_TYPE_NUMERIC:
			    	if (HSSFDateUtil.isCellDateFormatted(cell)) {
			    		Date  date = cell.getDateCellValue();
			    		return date;
			        }else{
			        	 Number number =  cell.getNumericCellValue();
						 return number;
			        }
			    	
			    case Cell.CELL_TYPE_STRING:
			        return cellValue.getStringValue();
			    case Cell.CELL_TYPE_BLANK:
			        break;
			    case Cell.CELL_TYPE_ERROR:
			        break;
			    // CELL_TYPE_FORMULA will never happen
			    case Cell.CELL_TYPE_FORMULA: 
			        break;
			}		
		 }
		 }
		 throw new UnsupportedOperationException("cell type "+cell.getCellType());
	 }

	 private String trimZero(Number n){
		 BigDecimal b = new BigDecimal(n.toString());
		 BigDecimal newData = b.stripTrailingZeros();
		 return newData.toString();
	 }

	public DBAccess getDb() {
		return db;
	}


	public void setDb(DBAccess db) {
		this.db = db;
	}





	public Workbook getBook() {
		return book;
	}


	public void setBook(Workbook book) {
		this.book = book;
	}


	public XLSLoader getLoader() {
		return loader;
	}


	public void setLoader(XLSLoader loader) {
		this.loader = loader;
	}


	public String getFile() {
		return file;
	}


	public void setFile(String file) {
		this.file = file;
	}


	public RowHolderFacotoy getRowFactory() {
		return rowFactory;
	}


	public void setRowFactory(RowHolderFacotoy rowFactory) {
		this.rowFactory = rowFactory;
	}
		

	
	 
	
	 
	  
	
	
}
